﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace LaoLuo.Data
{
    //undone
    public class QueryHelper
    {
        public QueryHelper(Func<string, SqlParameter[], IDataReader> getReader, Func<string, SqlParameter[], object> getSingle)
        {
            this.GetReader = getReader;
            this.GetSingle = getSingle;
        }

        protected Func<string, SqlParameter[], IDataReader> GetReader { get; set; }

        protected Func<string, SqlParameter[], object> GetSingle { get; set; }

        /// <summary>
        /// get the return value as int of a sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int GetInt(string sql, params SqlParameter[] parameters)
        {
            return Convert.ToInt32(GetSingle(sql, parameters));
        }

        /*public Tuple<int, IDataReader> GetPaginationResult(string sql, string orderBy, int limit, int offset, int needRowCount, params SqlParameter[] parameters)
        {
            var actualRowCount = Convert.ToInt32(GetSingle(ToGetCountSql(sql, needRowCount)));

            var reader = GetReader(ToSkipSql(sql, orderBy, limit, offset), parameters);

            return new Tuple<int, IDataReader>(actualRowCount, reader);
        }*/

        /// <summary>
        /// 此方法用于不获取总记录数的分页,根据参数获取一个二元组,
        /// </summary>
        /// <param name="sql">原始查询所用sql,不包括order by部分</param>
        /// <param name="orderBy">排序条件</param>
        /// <param name="pageSize">每页显示数据条数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageLimit">显示的页码数量</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public Tuple<int, IDataReader> GetPaginationResult(string sql, string orderBy, int pageSize, int pageIndex, int pageLimit, params SqlParameter[] parameters)
        {
            var offset = (pageIndex - 1)*pageSize;
            var needRowCount = (pageLimit - 1)*pageSize;

            var actualRowCount = GetInt(ToGetCountSql(sql, offset, needRowCount), parameters);
            var reader = GetPageReader(sql, orderBy, pageSize, offset, parameters);

            return new Tuple<int, IDataReader>(actualRowCount, reader);
        }

        public IDataReader GetPageReader(string sql, string orderBy, int limit, int skip = 0, params SqlParameter[] parameters)
        {
            return GetReader(ToSkipSql(sql, orderBy, limit, skip), parameters);
        }

        #region static
        /// <summary>
        /// 将查询语句转换为分页形式,支持sqlserver
        /// </summary>
        /// <param name="sql">query to make</param>
        /// <param name="orderBy">some string like "orderNo, id desc"</param>
        /// <param name="limit">max number of rows to return</param>
        /// <param name="skip">number of rows to skip</param>
        /// <returns>modified sql</returns>
        public static string ToSkipSql(string sql, string orderBy, int limit, int skip = 0)
        {
            if (!orderBy.ContainsIgnoreCase("ORDER BY"))
            {
                orderBy = "ORDER BY " + orderBy;
            }

            return
                string.Format(
                    @"
SELECT * FROM
    (
    SELECT tbl.*, ROW_NUMBER() OVER ({1}) rownum
    FROM ({0}) AS tbl
    ) seq
 WHERE seq.rownum BETWEEN {2} AND {3}", sql, orderBy, skip + 1, skip + limit);
        }

        /*public static Tuple<int, IDataReader> GetPaginationResult(Func<string, SqlParameter[], IDataReader> getReader, Func<string,object> getSingle, string sql, string orderBy, int limit, int offset, int needRowCount, params SqlParameter[] parameters)
        {
            var actualRowCount = Convert.ToInt32(getSingle(ToGetCountSql(sql, needRowCount)));

            var reader = getReader(ToSkipSql(sql, orderBy, limit, offset), parameters);

            return new Tuple<int, IDataReader>(actualRowCount, reader);
        }*/

        private static string ToGetCountSql(string sql, int offset, int needRowCount)
        {
            return
                string.Format(
                    @"
SELECT COUNT(*) FROM 
 (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn FROM
  ({0})
  AS t0)
 AS t1
WHERE rn > {1} AND rn < {2}
",
                    sql, offset, offset + needRowCount);
        }
        #endregion
    }
}
